BEGIN;
CREATE TABLE "MAIN_TIMETABLE" (
    "ID" integer NOT NULL PRIMARY KEY,
    "MONDAY_BEGIN" time NOT NULL,
    "MONDAY_END" time,
    "TUESDAY_BEGIN" time,
    "TUESDAY_END" time,
    "WEDNESDAY_BEGIN" time,
    "WEDNESDAY_END" time,
    "THURSDAY_BEGIN" time,
    "THURSDAY_END" time,
    "FRIDAY_BEGIN" time,
    "FRIDAY_END" time,
    "SATURDAY_BEGIN" time,
    "SATURDAY_END" time,
    "SUNDAY_BEGIN" time,
    "SUNDAY_END" time
)
;
CREATE GENERATOR "MAIN_TIMETABLE_GN"

            CREATE TRIGGER "MAIN_TIMETABLE_TR" FOR "MAIN_TIMETABLE"
            BEFORE INSERT
            AS 
            BEGIN
               IF (NEW."ID" IS NULL) THEN 
                   NEW."ID" = GEN_ID("MAIN_TIMETABLE_GN", 1);
            END
CREATE TABLE "MAIN_CLINIC" (
    "ID" integer NOT NULL PRIMARY KEY,
    "LEGAL_NAME" varchar(200) NOT NULL,
    "COMMERCIAL_NAME" varchar(200) NOT NULL,
    "SHORT_NAME" varchar(100),
    "REGION_ID" integer NOT NULL,
    "FACT_ADDRESS_ID" integer NOT NULL,
    "FACT_ADDRESS_HOUSE_NUMBER" integer NOT NULL,
    "FACT_ADDRESS_HOUSE_LETTER" varchar(1),
    "LEGAL_ADDRESS_POSTINDEX" integer NOT NULL,
    "LEGAL_ADDRESS_ID" integer NOT NULL,
    "LEGAL_ADDRESS_HOUSE_NUMBER" integer NOT NULL,
    "LEGAL_ADDRESS_HOUSE_LETTER" varchar(1) NOT NULL,
    "FAX" varchar(80),
    "EMAIL" varchar(75),
    "PATH" blob sub_type 1,
    "TIMETABLE_ID" integer REFERENCES "MAIN_TIMETABLE" ("ID")
)
;
CREATE GENERATOR "MAIN_CLINIC_GN"

            CREATE TRIGGER "MAIN_CLINIC_TR" FOR "MAIN_CLINIC"
            BEFORE INSERT
            AS 
            BEGIN
               IF (NEW."ID" IS NULL) THEN 
                   NEW."ID" = GEN_ID("MAIN_CLINIC_GN", 1);
            END
CREATE TABLE "MAIN_CLINICPHONE" (
    "ID" integer NOT NULL PRIMARY KEY,
    "OWNER_ID" integer NOT NULL REFERENCES "MAIN_CLINIC" ("ID"),
    "PHONE" varchar(20) NOT NULL,
    "LASTNAME" varchar(30),
    "FIRSTNAME" varchar(30),
    "DIVISION" varchar(80)
)
;
CREATE GENERATOR "MAIN_CLINICPHONE_GN"

            CREATE TRIGGER "MAIN_CLINICPHONE_TR" FOR "MAIN_CLINICPHONE"
            BEFORE INSERT
            AS 
            BEGIN
               IF (NEW."ID" IS NULL) THEN 
                   NEW."ID" = GEN_ID("MAIN_CLINICPHONE_GN", 1);
            END
CREATE TABLE "MAIN_SPECIALIST" (
    "ID" integer NOT NULL PRIMARY KEY,
    "CLINIC_ID" integer NOT NULL REFERENCES "MAIN_CLINIC" ("ID"),
    "LASTNAME" varchar(100) NOT NULL,
    "FIRSTNAME" varchar(100) NOT NULL,
    "FATHERNAME" varchar(100) NOT NULL,
    "POST_ID" integer NOT NULL,
    "ACADEMIC_DEGREE_ID" integer NOT NULL,
    "CATEGORY_ID" integer NOT NULL,
    "BEGIN_DATE" date NOT NULL,
    "END_DATE" date
)
;
CREATE GENERATOR "MAIN_SPECIALIST_GN"

            CREATE TRIGGER "MAIN_SPECIALIST_TR" FOR "MAIN_SPECIALIST"
            BEFORE INSERT
            AS 
            BEGIN
               IF (NEW."ID" IS NULL) THEN 
                   NEW."ID" = GEN_ID("MAIN_SPECIALIST_GN", 1);
            END
CREATE TABLE "MAIN_SERVICE" (
    "ID" integer NOT NULL PRIMARY KEY,
    "CATEGORY_ID" integer NOT NULL,
    "NAME" varchar(200) NOT NULL,
    "SHORT_NAME" varchar(20) NOT NULL
)
;
CREATE GENERATOR "MAIN_SERVICE_GN"

            CREATE TRIGGER "MAIN_SERVICE_TR" FOR "MAIN_SERVICE"
            BEFORE INSERT
            AS 
            BEGIN
               IF (NEW."ID" IS NULL) THEN 
                   NEW."ID" = GEN_ID("MAIN_SERVICE_GN", 1);
            END
CREATE TABLE "MAIN_REQUEST" (
    "ID" integer NOT NULL PRIMARY KEY,
    "INCOMING_PHONE" varchar(10) NOT NULL,
    "CONTACT_PHONE" varchar(10) NOT NULL,
    "REASON_ID" integer NOT NULL,
    "ANSWER_ID" integer NOT NULL,
    "SERVICE_ID" integer NOT NULL REFERENCES "MAIN_SERVICE" ("ID")
)
;
CREATE GENERATOR "MAIN_REQUEST_GN"

            CREATE TRIGGER "MAIN_REQUEST_TR" FOR "MAIN_REQUEST"
            BEFORE INSERT
            AS 
            BEGIN
               IF (NEW."ID" IS NULL) THEN 
                   NEW."ID" = GEN_ID("MAIN_REQUEST_GN", 1);
            END
CREATE TABLE "MAIN_TARIFF" (
    "ID" integer NOT NULL PRIMARY KEY,
    "CLINIC_ID" integer NOT NULL REFERENCES "MAIN_CLINIC" ("ID"),
    "SERVICE_ID" integer NOT NULL REFERENCES "MAIN_SERVICE" ("ID"),
    "SPECIALIST_ID" integer NOT NULL REFERENCES "MAIN_SPECIALIST" ("ID"),
    "COST" integer NOT NULL CHECK ("COST" >= 0),
    "BEGIN_DATE" date NOT NULL,
    "END_DATE" date
)
;
CREATE GENERATOR "MAIN_TARIFF_GN"

            CREATE TRIGGER "MAIN_TARIFF_TR" FOR "MAIN_TARIFF"
            BEFORE INSERT
            AS 
            BEGIN
               IF (NEW."ID" IS NULL) THEN 
                   NEW."ID" = GEN_ID("MAIN_TARIFF_GN", 1);
            END
CREATE TABLE "MAIN_CONTRACT" (
    "ID" integer NOT NULL PRIMARY KEY,
    "CLINIC_ID" integer NOT NULL REFERENCES "MAIN_CLINIC" ("ID"),
    "REG_NUM" integer NOT NULL CHECK ("REG_NUM" >= 0),
    "REG_DATE" date NOT NULL,
    "BEGIN_DATE" date NOT NULL,
    "END_DATE" date,
    "STATUS" integer NOT NULL
)
;
CREATE GENERATOR "MAIN_CONTRACT_GN"

            CREATE TRIGGER "MAIN_CONTRACT_TR" FOR "MAIN_CONTRACT"
            BEFORE INSERT
            AS 
            BEGIN
               IF (NEW."ID" IS NULL) THEN 
                   NEW."ID" = GEN_ID("MAIN_CONTRACT_GN", 1);
            END
CREATE TABLE "MAIN_BILL" (
    "ID" integer NOT NULL PRIMARY KEY,
    "CLINIC_ID" integer NOT NULL REFERENCES "MAIN_CLINIC" ("ID"),
    "CONTRACT_ID" integer NOT NULL REFERENCES "MAIN_CONTRACT" ("ID"),
    "SUM" integer NOT NULL CHECK ("SUM" >= 0),
    "REG_DATE" date NOT NULL,
    "TYPE" integer NOT NULL,
    "STATUS" integer NOT NULL
)
;
CREATE GENERATOR "MAIN_BILL_GN"

            CREATE TRIGGER "MAIN_BILL_TR" FOR "MAIN_BILL"
            BEFORE INSERT
            AS 
            BEGIN
               IF (NEW."ID" IS NULL) THEN 
                   NEW."ID" = GEN_ID("MAIN_BILL_GN", 1);
            END
CREATE TABLE "MAIN_DIRECTION" (
    "ID" integer NOT NULL PRIMARY KEY,
    "REQUEST_ID" integer NOT NULL REFERENCES "MAIN_REQUEST" ("ID"),
    "LASTNAME" varchar(100) NOT NULL,
    "FIRSTNAME" varchar(100) NOT NULL,
    "FATHERNAME" varchar(100) NOT NULL,
    "BIRTH_DATE" date NOT NULL,
    "SERVICE_DATE" date NOT NULL,
    "SERVICE_TIME" time NOT NULL,
    "CLINIC_ID" integer NOT NULL REFERENCES "MAIN_CLINIC" ("ID"),
    "SERVICE_ID" integer NOT NULL REFERENCES "MAIN_SERVICE" ("ID"),
    "SPECIALIST_ID" integer NOT NULL REFERENCES "MAIN_SPECIALIST" ("ID"),
    "RECEIVE_TYPE" integer NOT NULL,
    "DIRECTION_STATUS" integer NOT NULL,
    "STATUS" integer NOT NULL
)
;
CREATE GENERATOR "MAIN_DIRECTION_GN"

            CREATE TRIGGER "MAIN_DIRECTION_TR" FOR "MAIN_DIRECTION"
            BEFORE INSERT
            AS 
            BEGIN
               IF (NEW."ID" IS NULL) THEN 
                   NEW."ID" = GEN_ID("MAIN_DIRECTION_GN", 1);
            END
-- The following references should be added but depend on non-existent tables:
-- ALTER TABLE "MAIN_CLINIC" ADD CONSTRAINT "FACT_ADDRESS_ID_REFS_ID_CBF0022" FOREIGN KEY ("FACT_ADDRESS_ID") REFERENCES "KLADR_ADDRESS" ("ID");
-- ALTER TABLE "MAIN_CLINIC" ADD CONSTRAINT "LEGAL_ADDRESS_ID_REFS_ID_CBF414" FOREIGN KEY ("LEGAL_ADDRESS_ID") REFERENCES "KLADR_ADDRESS" ("ID");
-- ALTER TABLE "MAIN_SPECIALIST" ADD CONSTRAINT "CATEGORY_ID_REFS_ID_39F61938" FOREIGN KEY ("CATEGORY_ID") REFERENCES "REFERENCES_SPECIALISTCATEGORY" ("ID");
-- ALTER TABLE "MAIN_REQUEST" ADD CONSTRAINT "REASON_ID_REFS_ID_79C8F5A6" FOREIGN KEY ("REASON_ID") REFERENCES "REFERENCES_REQUESTREASON" ("ID");
-- ALTER TABLE "MAIN_REQUEST" ADD CONSTRAINT "ANSWER_ID_REFS_ID_15FFCDA8" FOREIGN KEY ("ANSWER_ID") REFERENCES "REFERENCES_REQUESTANSWER" ("ID");
-- ALTER TABLE "MAIN_SPECIALIST" ADD CONSTRAINT "ACADEMIC_DEGREE_ID_REFS_ID_1CC6" FOREIGN KEY ("ACADEMIC_DEGREE_ID") REFERENCES "REFERENCES_ACADEMICDEGREE" ("ID");
-- ALTER TABLE "MAIN_CLINIC" ADD CONSTRAINT "REGION_ID_REFS_ID_51A153B6" FOREIGN KEY ("REGION_ID") REFERENCES "REFERENCES_REGION" ("ID");
-- ALTER TABLE "MAIN_SPECIALIST" ADD CONSTRAINT "POST_ID_REFS_ID_4665606A" FOREIGN KEY ("POST_ID") REFERENCES "REFERENCES_SPECIALISTPOST" ("ID");
-- ALTER TABLE "MAIN_SERVICE" ADD CONSTRAINT "CATEGORY_ID_REFS_ID_76C8FA0C" FOREIGN KEY ("CATEGORY_ID") REFERENCES "REFERENCES_SERVICECATEGORY" ("ID");
CREATE INDEX "MAIN_CLINIC_REGION_ID" ON "MAIN_CLINIC" ("REGION_ID");
CREATE INDEX "MAIN_CLINIC_FACT_ADDRESS_ID" ON "MAIN_CLINIC" ("FACT_ADDRESS_ID");
CREATE INDEX "MAIN_CLINIC_LEGAL_ADDRESS_ID" ON "MAIN_CLINIC" ("LEGAL_ADDRESS_ID");
CREATE INDEX "MAIN_CLINIC_TIMETABLE_ID" ON "MAIN_CLINIC" ("TIMETABLE_ID");
CREATE INDEX "MAIN_CLINICPHONE_OWNER_ID" ON "MAIN_CLINICPHONE" ("OWNER_ID");
CREATE INDEX "MAIN_SPECIALIST_CLINIC_ID" ON "MAIN_SPECIALIST" ("CLINIC_ID");
CREATE INDEX "MAIN_SPECIALIST_POST_ID" ON "MAIN_SPECIALIST" ("POST_ID");
CREATE INDEX "MAIN_SPECIALIST_ACADEMIC_DE7C3F" ON "MAIN_SPECIALIST" ("ACADEMIC_DEGREE_ID");
CREATE INDEX "MAIN_SPECIALIST_CATEGORY_ID" ON "MAIN_SPECIALIST" ("CATEGORY_ID");
CREATE INDEX "MAIN_SERVICE_CATEGORY_ID" ON "MAIN_SERVICE" ("CATEGORY_ID");
CREATE INDEX "MAIN_REQUEST_REASON_ID" ON "MAIN_REQUEST" ("REASON_ID");
CREATE INDEX "MAIN_REQUEST_ANSWER_ID" ON "MAIN_REQUEST" ("ANSWER_ID");
CREATE INDEX "MAIN_REQUEST_SERVICE_ID" ON "MAIN_REQUEST" ("SERVICE_ID");
CREATE INDEX "MAIN_TARIFF_CLINIC_ID" ON "MAIN_TARIFF" ("CLINIC_ID");
CREATE INDEX "MAIN_TARIFF_SERVICE_ID" ON "MAIN_TARIFF" ("SERVICE_ID");
CREATE INDEX "MAIN_TARIFF_SPECIALIST_ID" ON "MAIN_TARIFF" ("SPECIALIST_ID");
CREATE INDEX "MAIN_CONTRACT_CLINIC_ID" ON "MAIN_CONTRACT" ("CLINIC_ID");
CREATE INDEX "MAIN_BILL_CLINIC_ID" ON "MAIN_BILL" ("CLINIC_ID");
CREATE INDEX "MAIN_BILL_CONTRACT_ID" ON "MAIN_BILL" ("CONTRACT_ID");
CREATE INDEX "MAIN_DIRECTION_REQUEST_ID" ON "MAIN_DIRECTION" ("REQUEST_ID");
CREATE INDEX "MAIN_DIRECTION_CLINIC_ID" ON "MAIN_DIRECTION" ("CLINIC_ID");
CREATE INDEX "MAIN_DIRECTION_SERVICE_ID" ON "MAIN_DIRECTION" ("SERVICE_ID");
CREATE INDEX "MAIN_DIRECTION_SPECIALIST_ID" ON "MAIN_DIRECTION" ("SPECIALIST_ID");
COMMIT;
